


////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////
//////////////////////// SRISK-C /////////////////////////
//////////////////////// /////////////////////////



/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS

* clear
clear
clear 		mata
mat 		drop _all

* set path
global		path	""								


set maxvar 32767





***************************************************************************
** 0. Load data for market value of sample banks
***************************************************************************
***************************************************************************


use "$path/0 Data/CRSP/CRSP_Banks_2019.dta", clear



ren TICKER Ticker

merge 	m:m Ticker using "$path/0 Data/_Processed/sample.dta"

keep if _merge == 3
drop _merge

gen MV = PRC * SHROUT

save "$path/0 Data/_Processed/CRSP_Banks_2019.dta", replace


***************************************************************************
** 01. Table 9 - Incremental SRISK (LRMES-C)
***************************************************************************
***************************************************************************



	
	**********************************
	* a) Table 9 Panel A
	***********************************
	

	preserve
	use "$path/0 Data/_Processed/InputUsageAnalysis/drawdown_qrt.dta", clear

		generate 	quarter = 	.	
		replace 	quarter = 	1 if month == 3
		replace 	quarter = 	2 if month == 6
		replace 	quarter = 	3 if month == 9
		replace 	quarter = 	4 if month == 12
			
		generate	yq 		= 	yq(year, quarter)
		format 		yq 			%tq	
			
			
		collapse 	(mean) UsagePctBoth, by(yq)
		drop 		if UsagePctBoth == .
			
		merge 		1:1 yq using "$path/0 Data/_Processed/sp500-ret-q"	
		keep 		if _merge == 3
		drop 		_merge

		merge 		1:1 yq using "$path/0 Data/_Processed/sp500-ret-q-lowest-cum"	
		keep 		if _merge == 3
		drop 		_merge
		
		
		tsset 		yq	
	
	
		* Add Q1 2020 as additional observation
		
		
		set 		obs 			`=	_N+1'
		replace 	yq 				= 	240 		if 	yq	==	.
		
		replace 	UsagePctBoth	=	0.381 		if 	UsagePctBoth==. 		// from the drawdown data (quarterly)
		replace 	return_sp_q		=	-0.2231567 	if 	return_sp_q==.
		replace 	return_sp_q_low		=	-0.3674092 	if 	return_sp_q_low==.
		
		
*		twoway (scatter UsagePctBoth return_sp_q) (lfit  UsagePctBoth return_sp_q )	
	
	
		set 		obs 			`=	_N+1'
		replace 	yq 				= 	239 		if 	yq	==	.
		
		replace 	UsagePctBoth	=	0.188 		if 	UsagePctBoth==. 		// from the drawdown data (quarterly)
		replace 	return_sp_q		=	.08189487	if 	return_sp_q==.
		replace 	return_sp_q_low		=	-.03039953 	if 	return_sp_q_low==.
	
	
		generate	covid = (yq >= 239)
		

		** Calculate the drawdown rates  
		***************************************
		
		
		* in 2020
		
			reg UsagePctBoth return_sp_q_low if covid == 1			
			
			
		
			display -0.4 * _b[return_sp_q_low] + _b[_cons] 
			
			
		* in 2007 - 2009	
			
			reg UsagePctBoth return_sp_q_low if covid == 0			
			
			
			display  -0.4 * _b[return_sp_q_low] + _b[_cons] 
			
		restore
			


		
		
		
	**********************************
	* b) Generate Table 9 - Panel B
	***********************************

	
	* load data
	*************
	
	use "$path/0 Data/_Processed/BHC_Bank_Ratios.dta", clear


	keep 		if 	year	==	2019
	keep		if	month	==	12
	
	rename		permco PERMCO
	
	drop		if PERMCO == .
	
merge 	m:m entity using "$path/0 Data/_Processed/sample.dta"
keep 	if _merge == 3
drop 	_merge
	

	
	* merge SRISK data
	*************************
	
	*rename		TICKER Ticker
	
	merge 		1:1 Ticker using "$path/0 Data/_Processed/srisk_31Dec2019"
	
	drop		if _merge == 2
	
	ren  _merge _merge_vlab
	
	merge m:m Ticker using "$path/0 Data/_Processed/CRSP_Banks_2019.dta"
	drop		if _merge == 2	
	drop _merge
	
	
	replace		MV	=	MV / 1000
	
	
	
	* Generate betas
	
	generate	beta_min	= -0.320
	generate	beta_max	= -0.555
	
	
	* Generate LRMES-C min and max
	***************************************
	
	generate	LRMES_C_min	=	liquidity_risk * (-beta_min)

	generate	LRMES_C_max	=	liquidity_risk * (-beta_max)
	
	
	* Generate Incremental SRISK-C
	************************************
	
	generate	SRISK_min	=	 LRMES_C_min * MV
	
	generate	SRISK_max	=	 LRMES_C_max * MV
		
	
	
	
	* Generate contingent liabilities
	// using drawdown rates in 2020 and 2007-2009
	*************************************************
	
	generate	contingent_liab_A		=	(offbs_unused_loans * 0.08 * 0.2579) / 1000
	generate	contingent_liab_B		=	(offbs_unused_loans * 0.08 * 0.3997) / 1000	

	
	

	* Generate Table 9 Panel B
	***********************************
	
	
	generate	debt = (lvg-1)*mv
	
	
	gsort		- assets_total
	
	
	order 	name offbs_unused_loans contingent_liab_A contingent_liab_B debt _merge
	
	
	generate	n = _n
	replace		n = . if n > 10
	
	
	
	preserve
	
	
	keep 	name offbs_unused_loans contingent_liab_A contingent_liab_B  debt _merge_vlab
	
	replace offbs_unused_loans = offbs_unused_loans / 1000
	
				
			foreach 	var of varlist offbs_unused_loans contingent_liab_A contingent_liab_B  debt {
					
					egen		total_`var'_top10	= sum(`var') if _n <= 10	
					egen		total_`var'_vlab	= sum(`var') if _merge_vlab == 3
					egen		total_`var'_All		= sum(`var') 
			
			} 
	
	
			drop		_merge 	

			export 		excel	using "$path/04 Results/Table 10B - long.xlsx", firstrow(variables) replace	
	

	restore		
	
	
	
	
	
	
	**********************************
	* c) Generate Table 9	Panel C
	***********************************
	
	gsort		- assets_total
	
	
	order name MV lrmes liquidity_risk beta_* LRMES* SRISK* _merge_vlab
	

	
		
	* Incremental SRISK MES
	********************************		
		
		preserve
		
			keep	name MV lrmes liquidity_risk beta_* LRMES* SRISK* _merge_vlab
	
				
			foreach 	var of varlist MV LRMES* SRISK* {
					
					egen		total_`var'_top10	= sum(`var') if _n <= 10	
					egen		total_`var'_vlab	= sum(`var') if _merge_vlab == 3
					egen		total_`var'_All		= sum(`var') 
			
			} 
	
	
		*	drop		_merge 	

			export 		excel	using "$path/04 Results/Table 10 C - long.xlsx", firstrow(variables) replace	
	
	
		restore	
	
	
	
	
	
	**********************************
	* d) Generate Table 9 Panel D
	***********************************
	
	gsort		- MV
	
	
	order name MV srisk lrmes liquidity_risk beta_* LRMES* SRISK* _merge_vlab
	
	
	generate	srisk2	=	srisk
	
	order 		name MV srisk srisk2
	
	replace		srisk = 0 if srisk < 0
	
	
	* Generate SRISK-C  
	*****************************
	
	generate	SRISK_C_min	=	contingent_liab_A + SRISK_min
	generate	SRISK_C_max	=	contingent_liab_B + SRISK_max	
	
	
	
		
	
	* Incremental SRISK MES
	********************************		
		
		preserve
		
			keep	name MV srisk srisk2 SRISK_C_min SRISK_C_max _merge_vlab
	
				
			foreach 	var of varlist srisk srisk2 SRISK_C_min SRISK_C_max _merge_vlab {
					
					egen		total_`var'_top10	= sum(`var') if _n <= 10	
					egen		total_`var'_vlab	= sum(`var') if _merge_vlab == 3
					egen		total_`var'_All		= sum(`var') 
			
			} 
	
	
			drop		_merge 	

			export 		excel	using "$path/04 Results/Table 10 D.xlsx", firstrow(variables) replace	
	
	
		restore		
	
	
	
* eof



	
	

	
